package com.yenlien.traceyun.control.dao;

import com.yenlien.traceyun.control.entity.Shelves;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author jialong
 * @date 2021/05/19
 * @description: 货架数据层
 **/
public interface ShelvesDao extends BaseJpaDao<Shelves> {

    Shelves findById(Long id);

    Shelves findByName(String name);

    @Query(value = "SELECT * FROM shelves WHERE position_id IN (?1)", nativeQuery = true)
    List<Shelves> findByPositionIds(List<Long> positionIds);

    @Query(value = "SELECT * FROM shelves WHERE position_id = ?1", nativeQuery = true)
    List<Shelves> findByPositionId(Long positionId);

    // 获取货架里有货物的货架信息
    @Query(value = "SELECT s.* FROM shelves s JOIN goods g ON s.id = g.shelves_id WHERE s.position_id IN (?1)", nativeQuery = true)
    List<Shelves> findShelvesGoods(List<Long> positionIds);


    @Query(value = "SELECT s.* FROM shelves s LEFT JOIN goods g ON s.id = g.shelves_id WHERE g.id IS NULL AND s.position_id IN (?1)", nativeQuery = true)
    List<Shelves> findShelvesNoneGoods(List<Long> positionIds);

    @Query(value = "SELECT * FROM shelves WHERE position_id = ?1", nativeQuery = true)
    List<Shelves> getShelvesByPosition(Long positionId);
}
